Export to Excel with indentation

Hello all,

 I'm working on a bit of custom DXL that includes some exporting to Excel and setting various Excel properties on the output and I'm stumped.  I'm trying to indent within a cell and so far I've not been able to get it to work.  But I've been able to set other properties without issue.

Here's how I tried it with "olePut" ( trying to mimic the VBA that is recorded when I right-click a cell and select Format Cells and change the indent property on the alignment tab):

 s_result = olePut ( objExcelSelection, "HorizontalAlignment", xlLeft )
print "HorizontalAlignment: " s_result "\n"
s_result = olePut ( objExcelSelection, "IndentLevel", 1 )
print "IndentLevel: " s_result "\n"
 

The "HorizontalAlignment" part works, but the "IndentLevel" does not. 

Here's how I tried it with "oleMethod" (trying to mimic the VBA that is recorded when I use the toolbar increase indent button on a cell):

put ( objArgs, 1 )
s_result = oleMethod ( objExcelRange, "InsertIndent", objArgs )
print "InsertIndent: " s_result "\n"
clear objArgs

It doesn't work, but if I try to use other methods with oleMethod they work.

I've also tried each of these with both ranges and selections and had no luck, and I've tried using both bits of code together with no luck.

Does anyone have any idea where I'm going wrong?

Thanks!

Re: Export to Excel with indentation
GregM_dxler - Thu Nov 14 11:25:53 EST 2013

According to Microsoft.  IndentLevel is a Property, so you would need to use the olePut command for the cell property.  Maybe try the oleGet to see what the current value is.  Maybe 1 is too small to be noticed.  In their library, their example is setting the indentlevel to 15.

Hope this helps,

Greg

Re: Export to Excel with indentation
emily.butt - Thu Nov 14 13:10:58 EST 2013

Very strange.  If I do an oleGet before I use olePut, I get 0.  Then if I do another oleGet after I use olePut,  I get 1 or whatever I tried to set it to with olePut.  But when I go to the Excel output, it remains zero, confirmed using the popup window used to manually change the properties as well as querying the property with some VBA (and because it just looks wrong).  Likewise for the second approach, using oleGet on IndentLevel before and after using the InsertIndent method with oleMethod instead of the olePut approach.

Even more interesting, when I tried bringing the olePut (or oleMethod) command out of the loop where I'm doing the export to the cells and instead going back at the end of the script and changing the indent of the cells with one of these commands, it works!

So I guess both approaches work (using the InsertIndent method or changing the IndentLevel property), just not in the export loop, even though doing it in the export loop oleGet would lead one to believe that DXL thinks it made the change to the property.  Such complex subtleties!

Thanks so much for your help, Greg!